package com.woniu.dao.impl;

import com.woniu.dao.StudentDao;
import com.woniu.entity.Student;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    private static Connection con() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/db1";
        String user = "root";
        String password = "123";
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }

    @Override
    public Student selectStudentone(int id) throws Exception{
        QueryRunner qr = new QueryRunner();
        String sql = "select * from student where id = ?";
        Connection connection = con();
        Student student=qr.query(connection,sql,new BeanHandler<Student>(Student.class),id);
        return student;
    }
    @Override
    public List<Student> selectStudentall() throws Exception {
        QueryRunner qr = new QueryRunner();
        String sql = "select * from student";
        Connection connection = con();
        List<Student> list= qr.query(connection,sql,new BeanListHandler<Student>(Student.class));
        return list;
    }

    @Override
    public int insertStudent(Student student) throws Exception {
        QueryRunner qr = new QueryRunner();
        String sql = "insert into student values(?,?,?,?)";
        Connection connection = con();
        int update = qr.update(connection, sql, student.getId(), student.getName(), student.getAge(),student.getPassword());
        return update;
    }

    @Override
    public int updateStudent(Student student) throws Exception {
        QueryRunner qr = new QueryRunner();
        String sql = "update student set name=?,age=?,password=? where id=?";
        Connection connection = con();
        int update = qr.update(connection, sql,student.getName(),student.getAge(),student.getPassword(),student.getId());
        return update;
    }

    @Override
    public int deleteStudent(int id) throws Exception {
        QueryRunner qr = new QueryRunner();
        String sql = "delete from student where id = ?";
        Connection connection = con();
        int update = qr.update(connection, sql,id);
        return update;
    }
}
